Day11 Except, In

Except 可以用在權限控管

例如

-- TableA
| ID  | Name    |
| --- | ------- |
| 1   | Alice   |
| 2   | Bob     |
| 3   | Charlie |


-- TableB
| ID  | Name    |
| --- | ------- |
| 2   | Bob     |
| 3   | Charlie |
| 4   | David   |

我們可以下語法，在 TableA 且不在 Table B裡面的資料

SELECT ID, Name
FROM TableA
EXCEPT
SELECT ID, Name
FROM TableB;

結果

| ID  | Name  |
| --- | ----- |
| 1   | Alice |

這種時候 EXCEPT 可以用在權限控管系統
例如 TableB 是 admin 權限，TableA 是一般使用者，使用者只能看到其他使用者而不能看到 admin 權限的使用者

In

CREATE TABLE Employee (
    ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Department VARCHAR(100)
);

CREATE TABLE BonusList (
    ID INT PRIMARY KEY,
    Department VARCHAR(100),
    IsBonus INT -- 1 表示有獎金，0 表示沒有獎金
);

INSERT INTO Employee (ID, Name, Department) VALUES
(1, 'Alice', 'Marketing'),
(2, 'Bob', 'Purchase'),
(3, 'Charlie', 'IT'),
(4, 'David', 'Sales'),
(5, 'Eve', 'HR');

INSERT INTO BonusList (ID, Department, IsBonus) VALUES
(1, 'Sales', 1),
(2, 'HR', 1),
(3, 'IT', 1),
(4, 'Purchase', 0),
(5, 'Marketing', 0);

表的樣子

Table Employee
| ID  | Name    | Department |
| --- | ------- | ---------- |
| 1   | Alice   | Sales      |
| 2   | Bob     | HR         |
| 3   | Charlie | IT         |
| 4   | David   | Sales      |
| 5   | Eve     | HR         |


Table BonusList
| ID  | Department  | IsBonus |
| --- | ----------- | --------|
| 1   | Sales       | 1       |
| 2   | HR          | 1       |
| 3   | IT          | 1       |
| 4   | Purchase    | 0       |
| 5   | Marketing   | 0       |

假設公司想要發獎勵，Sales, HR, IT 三個部門業績達標
我們就可以下

select Name 
from Employee
where Department in (
   select Department from BonusList where IsBonus = 1
)  

結果

name
----
"Charlie"
"David"
"Eve"